import pymysql
import numpy as np
from functools import lru_cache

cnn = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306, database='flask_douban_comment',
                      charset='utf8')

def get_ratings():
    cursor = cnn.cursor()
    sql = 'select uid, iid, rate from tb_rate'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    return data

@lru_cache(maxsize=128)
def recommend(userId, top_n=5):
    data = get_ratings()
    users = list(sorted(set([d[0] for d in data])))
    items = list(sorted(set([d[1] for d in data])))
    user_idx = {u: i for i, u in enumerate(users)}
    item_idx = {i: j for j, i in enumerate(items)}
    R = np.zeros((len(users), len(items)))
    for u, i, r in data:
        R[user_idx[u], item_idx[i]] = r
    # SVD分解
    try:
        U, s, Vt = np.linalg.svd(R, full_matrices=False)
    except Exception:
        return []
    S = np.diag(s)
    user_vecs = np.dot(U, S)  # 用户隐向量
    item_vecs = Vt.T           # 物品隐向量
    uidx = user_idx.get(userId, None)
    if uidx is None:
        return []
    # 用户兴趣中心
    watched = [item_idx[i] for u, i, r in data if u == userId]
    if not watched:
        return []
    center = np.mean(item_vecs[watched], axis=0)
    # 推荐与兴趣中心最远的高分电影
    dists = []
    for j in range(len(items)):
        if j in watched:
            continue
        dist = np.linalg.norm(item_vecs[j] - center)
        avg_score = np.mean([r for u, i, r in data if i == items[j]])
        dists.append((items[j], dist, avg_score))
    # 先按距离降序，再按平均分降序
    dists = sorted(dists, key=lambda x: (x[1], x[2]), reverse=True)
    # 去重，只保留前top_n个
    seen = set()
    unique_result = []
    for mid, dist, avg_score in dists:
        if mid not in seen:
            unique_result.append((int(mid), float(dist)))
            seen.add(mid)
        if len(unique_result) >= top_n:
            break
    # 查电影详情，补全所有字段
    if not unique_result:
        return []
    movie_ids = [mid for mid, _ in unique_result]
    cursor = cnn.cursor()
    format_strings = ','.join(['%s'] * len(movie_ids))
    sql = f"SELECT id, douban_id, cover, name, alias, douban_score, douban_votes, directors, actors, year, regions, genres, storyline, release_date FROM movies2 WHERE id IN ({format_strings})"
    cursor.execute(sql, tuple(movie_ids))
    movies = cursor.fetchall()
    cursor.close()
    movie_dict = {row[0]: row for row in movies}
    result = []
    for mid, score in unique_result:
        info = movie_dict.get(mid)
        if info:
            result.append({
                'id': info[0] or '',
                'douban_id': info[1] or '',
                'cover': info[2] or '',
                'name': info[3] or '',
                'alias': info[4] or '',
                'douban_score': float(info[5]) if info[5] is not None else 0.0,
                'douban_votes': info[6] or 0,
                'directors': info[7] or '',
                'actors': info[8] or '',
                'year': info[9] or '',
                'regions': info[10] or '',
                'genres': info[11] or '',
                'storyline': info[12] or '',
                'release_date': info[13] or ''
            })
    return result 